package shop.coffeeshop.master;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;


public class ShopCoffeeDBBean {

	private static ShopCoffeeDBBean instance = new ShopCoffeeDBBean();

	public static ShopCoffeeDBBean getInstance(){
		return instance;
	}

	private ShopCoffeeDBBean(){}

	//커넥션 풀로 커넥션 객체를 얻어낸다
	private Connection getConnection() throws Exception{
		Context initCtx = new InitialContext();
		Context envCtx = (Context)initCtx.lookup("java:comp/env");
		DataSource ds = (DataSource)envCtx.lookup("jdbc/coffee");
		return ds.getConnection();
	}

	public int managerCheck(String id,String passwd)	throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String dbpasswd="";
		int x = -1;
		try{
			conn = getConnection();
			pstmt = conn.prepareStatement("select managerPasswd from manager where managerId = ?");
			pstmt.setString(1, id);

			rs = pstmt.executeQuery();

			if(rs.next()){
				dbpasswd=rs.getString("managerPasswd");
				if(dbpasswd.equals(passwd))
					x = 1; //인증성공
				else
					x = 0;//비밀번호 틀림

			}else
				x= -1; //해당아이디 없음


		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
		return x;
	}

	//커피등록 메소드

	public void insertCoffee(ShopCoffeeDataBean coffee) throws Exception {
		Connection conn  = null;
		PreparedStatement pstmt = null;

		try{
			conn = getConnection();

			pstmt = conn.prepareStatement("insert into coffee values (?,?,?,?,?,?,?,?,?,?,?,?)");
			pstmt.setInt(1, coffee.getCoffee_id());
			pstmt.setString(2, coffee.getCoffee_kind());
			pstmt.setString(3, coffee.getCoffee_title());
			pstmt.setInt(4, coffee.getCoffee_price());
			pstmt.setShort(5, coffee.getCoffee_count());
			pstmt.setString(6, coffee.getAuthor());
			pstmt.setString(7, coffee.getPublishing_com());
			pstmt.setString(8, coffee.getPublishing_date());
			pstmt.setString(9, coffee.getCoffee_image());
			pstmt.setString(10, coffee.getCoffee_content());
			pstmt.setByte(11, coffee.getDiscount_rate());
			pstmt.setTimestamp(12, coffee.getReg_date());

			pstmt.executeUpdate();

		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
	}

	//이미 등록된 커피를 검증

	public int registedCoffeeconfirm(String kind,String coffeeName, String author)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int x = -1;

		try{
			conn = getConnection();

			String sql = "select coffee_name form coffee";
			sql += " where coffee_kind = ? and coffee_name =? and author = ?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, kind);
			pstmt.setString(2, coffeeName);
			pstmt.setString(3, author);

			rs = pstmt.executeQuery();

			if(rs.next())
				x=1;//해당 제품이 이미 있음
			else
				x=-1;


		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
		return x;
	}


	//전체 등록된 상품수를 얻어내는 메소드
	public int getCoffeeCount() throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		int x = 0;

		try{
			conn = getConnection();

			pstmt=conn.prepareStatement("select count(*)from coffee");
			rs = pstmt.executeQuery();

			if(rs.next())
				x=rs.getInt(1);
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}

		return x;
	}

	//분류별 또는 전체 등록된 상품의 정보를 얻어내는 메소드
	public List<ShopCoffeeDataBean> getCoffee(String coffee_kind) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		List<ShopCoffeeDataBean>coffeeList = null;

		try{
			conn = getConnection();

			String sql1 = "select * from coffee";
			String sql2 = "select * from coffee ";
			sql2 += "where coffee_kind = ? order by reg_date desc";

			if(coffee_kind.equals("all")){
				pstmt = conn.prepareStatement(sql1);
			}else{
				pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1, coffee_kind);
			}
			rs = pstmt.executeQuery();

			if(rs.next()){
				coffeeList = new ArrayList<ShopCoffeeDataBean>();
				do{
					ShopCoffeeDataBean coffee = new ShopCoffeeDataBean();

					coffee.setCoffee_id(rs.getInt("coffee_id"));
					coffee.setCoffee_kind(rs.getString("coffee_kind"));
					coffee.setCoffee_title(rs.getString("coffee_title"));
					coffee.setCoffee_price(rs.getInt("coffee_price"));
					coffee.setCoffee_count(rs.getShort("coffee_count"));
					coffee.setAuthor(rs.getString("author"));
					coffee.setPublishing_com(rs.getString("publishing_com"));
					coffee.setPublishing_date(rs.getString("publishing_date"));
					coffee.setCoffee_image(rs.getString("coffee_image"));
					coffee.setDiscount_rate(rs.getByte("discount_rate"));
					coffee.setReg_date(rs.getTimestamp("reg_date"));

					coffeeList.add(coffee);

				}while(rs.next());
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
		return coffeeList;
	}


	//쇼핑몰 메인에 표시하기 위해 사용하는 분류별 신제품 목록을 얻어내는 메소드
	public ShopCoffeeDataBean[] getCoffee(String coffee_kind,int count)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ShopCoffeeDataBean coffeeList[]=null;
		int i = 0;

		try{
			conn = getConnection();

			String sql = "select * from coffee where coffee_kind = ? ";
			sql += "order by reg_date desc limit ?,?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, coffee_kind);
			pstmt.setInt(2, 0);
			pstmt.setInt(3, count);
			rs = pstmt.executeQuery();

			if(rs.next()){
				coffeeList = new ShopCoffeeDataBean[count];
				do{
					ShopCoffeeDataBean coffee = new ShopCoffeeDataBean();

					coffee.setCoffee_id(rs.getInt("coffee_id"));
					coffee.setCoffee_kind(rs.getString("coffee_kind"));
					coffee.setCoffee_title(rs.getString("coffee_title"));
					coffee.setCoffee_price(rs.getInt("coffee_price"));
					coffee.setCoffee_count(rs.getShort("coffee_count"));
					coffee.setAuthor(rs.getString("author"));
					coffee.setPublishing_com(rs.getString("publishing_com"));
					coffee.setPublishing_date(rs.getString("publishing_date"));
					coffee.setCoffee_image(rs.getString("coffee_image"));
					coffee.setDiscount_rate(rs.getByte("discount_rate"));
					coffee.setReg_date(rs.getTimestamp("reg_date"));

					coffeeList[i]=coffee;

					i++;

				}while(rs.next());
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
		return coffeeList;
	}

	//CoffeeId 에 해당하는 커피의 정보를 얻어내는 메소드
	//등록된 커피를 수정하기 위해 수정 폼으로 읽어들이기 위한 메소드
	public ShopCoffeeDataBean getCoffee(int coffeeId) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ShopCoffeeDataBean coffee=null;

		try{
			conn = getConnection();

			pstmt = conn.prepareStatement("select * from coffee where coffee_id = ?");
			pstmt.setInt(1, coffeeId);

			rs = pstmt.executeQuery();

			if(rs.next()){
				coffee = new ShopCoffeeDataBean();

				coffee.setCoffee_kind(rs.getString("coffee_kind"));
				coffee.setCoffee_title(rs.getString("coffee_title"));
				coffee.setCoffee_price(rs.getInt("coffee_price"));
				coffee.setCoffee_count(rs.getShort("coffee_count"));
				coffee.setAuthor(rs.getString("author"));
				coffee.setPublishing_com(rs.getString("publishing_com"));
				coffee.setPublishing_date(rs.getString("publishing_date"));
				coffee.setCoffee_image(rs.getString("coffee_image"));
				coffee.setCoffee_content(rs.getString("coffee_content"));
				coffee.setDiscount_rate(rs.getByte("discount_rate"));
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
		return coffee;
	}
	//등록된 커피의 정보를 수정시 사용하는 메소드

	public void updateCoffee(ShopCoffeeDataBean coffee, int coffeeId) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql;

		try{

			conn = getConnection();

			sql = "update coffee set coffee_kind=?,coffee_title=?,coffee_price=?";
			sql += ",coffee_count=?,author=?,publishing_com=?,publishing_date=?";
			sql += ",coffee_image=?,coffee_content=?,discount_rate=?";
			sql += " where coffee_id=?";

			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, coffee.getCoffee_kind());
			pstmt.setString(2, coffee.getCoffee_title());
			pstmt.setInt(3, coffee.getCoffee_price());
			pstmt.setShort(4, coffee.getCoffee_count());
			pstmt.setString(5, coffee.getAuthor());
			pstmt.setString(6, coffee.getPublishing_com());
			pstmt.setString(7, coffee.getPublishing_date());
			pstmt.setString(8, coffee.getCoffee_image());
			pstmt.setString(9, coffee.getCoffee_content());
			pstmt.setByte(10, coffee.getDiscount_rate());
			pstmt.setInt(11, coffeeId);			
			
			pstmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
	}
	
	//coffeeId에 해당하는 커피의 정보를 삭제시 사용하는 메소드
	public void deleteCoffee(int coffeeId) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("delete from coffee where coffee_id=?");
			pstmt.setInt(1, coffeeId);
			pstmt.executeUpdate();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
			if(conn!=null) try{conn.close();}catch(SQLException ex){}
		}
	}
}